package com.zhht.checkcop.db;

import org.apache.log4j.Logger;

import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MysqlDb {
    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement prst = null;
    private static ResultSet rst = null;
    private static ResultSetMetaData rsmd = null;
    private static PooledDBA connPool = null;

    public static Formatter fmt = new Formatter();
    private final static Logger log = Logger.getLogger(MysqlDb.class);

    static {
        connPool = new PooledDBA();
    }

//	//更新作者表的作品数量
//	public static void main(String[] args) throws Exception {
//		MysqlDb mysqldb = new MysqlDb();
//		mysqldb.connect();
//		List<Map<String, Object>> list = mysqldb.query("select `id`,`name` from t_gf_authors");
//		for(Map<String,Object> mm:list){
//			try{
//				System.out.println("更新："+mm.get("name"));
//				String id = mm.get("id")+"";
//				String name = mm.get("name")+"";
//				List<Map<String, Object>> query = mysqldb.query("select kind_one,count(1) num from t_gf_book where author = '"+name+"' GROUP BY kind_one");
//				if(query!=null&&query.size()>0){
//					String si="0";
//					String ci="0";
//					String qu="0";
//					String fu="0";
//					String shu="0";
//					String wen="0";
//					Integer count=0;
//					for(Map<String,Object> map:query){
//						if("1".equals(map.get("kind_one").toString())){
//							si = map.get("num")+"";
//						}
//						if("2".equals(map.get("kind_one").toString())){
//							ci = map.get("num")+"";
//						}
//						if("3".equals(map.get("kind_one").toString())){
//							qu = map.get("num")+"";
//						}
//						if("4".equals(map.get("kind_one").toString())){
//							fu = map.get("num")+"";
//						}
//						if("5".equals(map.get("kind_one").toString())){
//							shu = map.get("num")+"";
//						}
//						if("6".equals(map.get("kind_one").toString())){
//							wen = map.get("num")+"";
//						}
//						count += Integer.parseInt(map.get("num")+"");
//					}
//					
//					mysqldb.execute("update t_gf_authors set works_count = '"+count+"',works_shi_count = '"+si+"',"
//							+ "works_ci_count = '"+ci+"',works_qu_count = '"+qu+"',works_fu_count = '"+fu+"',"
//									+ "works_shu_count = '"+shu+"',works_wen_count = '"+wen+"' where id = "+id);
//				}
//			}catch (Exception e) {
//				System.out.println(e.toString());
//			}
//		}
//	}
// //统计子集的json
//    public static void main(String[] args) throws Exception {
//    	MysqlDb mysqldb = new MysqlDb();
//    	mysqldb.connect();
//    	List<Map<String, Object>> list = mysqldb.query("select * from t_gf_lab where parentId = 0 and del = 0 and type = 8");
//    	for(Map<String,Object> mm:list){
//    		try{
//    			System.out.println("更新："+mm.get("categoryName"));
//	    		String id = mm.get("id")+"";
//	    		//type != 8 用下面的
//	    		List<Map<String, Object>> query = mysqldb.query("select id,categoryContentNum,categoryName,categoryPicUrl,type,extraId from t_gf_lab where parentId = "+id+" and del = 0 order by showOrder");
//	    		if(query!=null&&query.size()>0){
//	    			String content = JSONObject.toJSONString(query);
//	    			mysqldb.execute("update t_gf_lab set childJson = '"+content+"' where id = "+id);
//	    		}
//    		}catch (Exception e) {
//    			System.out.println(e.toString());
//			}
//    	}
//    }


    public static void main(String[] args) throws Exception {
        String picUrl = "http://file.diando.net/img/book/book_cover_395604.jpg,http://file.diando.net/img/book/book_cover_395619.jpg,http://file.diando.net/img/book/book_cover_395638.jpg,http://file.diando.net/img/book/book_cover_395664.jpg,http://file.diando.net/img/book/book_cover_395682.jpg,http://file.diando.net/img/book/book_cover_395686.jpg,http://file.diando.net/img/book/book_cover_395696.jpg,http://file.diando.net/img/book/book_cover_395700.jpg,http://file.diando.net/img/book/book_cover_395707.jpg,http://file.diando.net/img/book/book_cover_395708.jpg,http://file.diando.net/img/book/book_cover_395726.jpg,http://file.diando.net/img/book/book_cover_395763.jpg,http://file.diando.net/img/book/book_cover_395764.jpg,http://file.diando.net/img/book/book_cover_395766.jpg,http://file.diando.net/img/book/book_cover_395767.jpg,http://file.diando.net/img/book/book_cover_395822.jpg,http://file.diando.net/img/book/book_cover_395823.jpg,http://file.diando.net/img/book/book_cover_395825.jpg,http://file.diando.net/img/book/book_cover_395877.jpg,http://file.diando.net/img/book/book_cover_395929.jpg,http://file.diando.net/img/book/book_cover_395950.jpg,http://file.diando.net/img/book/book_cover_395978.jpg,http://file.diando.net/img/book/book_cover_396029.jpg,http://file.diando.net/img/book/book_cover_396055.jpg,http://file.diando.net/img/book/book_cover_396100.jpg,http://file.diando.net/img/book/book_cover_396117.jpg,http://file.diando.net/img/book/book_cover_396137.jpg,http://file.diando.net/img/book/book_cover_396199.jpg,http://file.diando.net/img/book/book_cover_396201.jpg,http://file.diando.net/img/book/book_cover_396234.jpg,http://file.diando.net/img/book/book_cover_396243.jpg,http://file.diando.net/img/book/book_cover_396262.jpg,http://file.diando.net/img/book/book_cover_396293.jpg,http://file.diando.net/img/book/book_cover_396307.jpg,http://file.diando.net/img/book/book_cover_396311.jpg,http://file.diando.net/img/book/book_cover_396335.jpg,http://file.diando.net/img/book/book_cover_396369.jpg,http://file.diando.net/img/book/book_cover_396393.jpg,http://file.diando.net/img/book/book_cover_396409.jpg,http://file.diando.net/img/book/book_cover_396418.jpg,http://file.diando.net/img/book/book_cover_396421.jpg,http://file.diando.net/img/book/book_cover_396485.jpg,http://file.diando.net/img/book/book_cover_396542.jpg,http://file.diando.net/img/book/book_cover_396550.jpg,http://file.diando.net/img/book/book_cover_396559.jpg,http://file.diando.net/img/book/book_cover_396573.jpg,http://file.diando.net/img/book/book_cover_396601.jpg,http://file.diando.net/img/book/book_cover_396639.jpg,http://file.diando.net/img/book/book_cover_396653.jpg,http://file.diando.net/img/book/book_cover_396680.jpg,http://file.diando.net/img/book/book_cover_396683.jpg,http://file.diando.net/img/book/book_cover_396686.jpg,http://file.diando.net/img/book/book_cover_396710.jpg,http://file.diando.net/img/book/book_cover_396717.jpg,http://file.diando.net/img/book/book_cover_396721.jpg,http://file.diando.net/img/book/book_cover_396740.jpg,http://file.diando.net/img/book/book_cover_396750.jpg,http://file.diando.net/img/book/book_cover_396835.jpg,http://file.diando.net/img/book/book_cover_396896.jpg,http://file.diando.net/img/book/book_cover_396901.jpg,http://file.diando.net/img/book/book_cover_396908.jpg,http://file.diando.net/img/book/book_cover_397058.jpg,http://file.diando.net/img/book/book_cover_397100.jpg,http://file.diando.net/img/book/book_cover_397102.jpg,http://file.diando.net/img/book/book_cover_397137.jpg,http://file.diando.net/img/book/book_cover_397138.jpg,http://file.diando.net/img/book/book_cover_397147.jpg,http://file.diando.net/img/book/book_cover_397154.jpg,http://file.diando.net/img/book/book_cover_397173.jpg,http://file.diando.net/img/book/book_cover_397177.jpg,http://file.diando.net/img/book/book_cover_397197.jpg,http://file.diando.net/img/book/book_cover_397261.jpg,http://file.diando.net/img/book/book_cover_397270.jpg,http://file.diando.net/img/book/book_cover_397309.jpg,http://file.diando.net/img/book/book_cover_397336.jpg,http://file.diando.net/img/book/book_cover_397342.jpg,http://file.diando.net/img/book/book_cover_397344.jpg,http://file.diando.net/img/book/book_cover_397354.jpg,http://file.diando.net/img/book/book_cover_397386.jpg,http://file.diando.net/img/book/book_cover_397388.jpg,http://file.diando.net/img/book/book_cover_402252.jpg,http://file.diando.net/img/book/book_cover_402309.jpg,http://file.diando.net/img/book/book_cover_402543.jpg,http://file.diando.net/img/book/book_cover_402585.jpg,http://file.diando.net/img/book/book_cover_411261.jpg,http://file.diando.net/img/book/book_cover_412258.jpg,http://file.diando.net/img/book/book_cover_412276.jpg";

        for (String str : picUrl.split(",")) {
            Download(str);
        }

    }

    private static String Download(String urlList) {
        URL url = null;
        String filepath = "C:\\Users\\Administrator\\Desktop\\aaaaaa";
        try {
            url = new URL(urlList);
            DataInputStream dataInputStream = new DataInputStream(url.openStream());
            try {
                File file = new File(filepath);
                if (!file.exists()) {
                    file.mkdirs();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            filepath = "C:\\Users\\Administrator\\Desktop\\aaaaaa\\" + urlList.substring(urlList.lastIndexOf("/") + 1, urlList.length()) + ".jpg";
            FileOutputStream fileOutputStream = new FileOutputStream(new File(filepath));
            ByteArrayOutputStream output = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int length;
            while ((length = dataInputStream.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
            fileOutputStream.write(output.toByteArray());
            dataInputStream.close();
            fileOutputStream.close();
        } catch (MalformedURLException e) {
            System.out.println(urlList);
        } catch (IOException e) {
            System.out.println(urlList);
//            e.printStackTrace();
        }
        return filepath;
    }

//    //统计作品数量
//    public static void main(String[] args) throws Exception {
//    	MysqlDb mysqldb = new MysqlDb();
//    	mysqldb.connect();
//    	List<Map<String, Object>> list = mysqldb.query("select id,`categoryName` from t_gf_lab where parentId != 0 AND type in(8) and del = 0");
//    	for(Map<String,Object> mm:list){
//    		try{
//    			System.out.println("更新："+mm.get("categoryName"));
//    			String name = mm.get("categoryName")+"";
//    			String id = mm.get("id")+"";
//    			List<Map<String, Object>> query = mysqldb.query("select count(1) count from t_gf_book where author = '"+name+"'");
//    			if(query!=null&&query.size()>0){
//    				Integer count = Integer.parseInt(query.get(0).get("count")+"");
//    				mysqldb.execute("update t_gf_lab set categoryContentNum = "+count+" where id = "+id);
//    			}
//    		}catch (Exception e) {
//    			System.out.println(e.toString());
//    		}
//    	}
//    }

//	public static void main(String[] args) throws Exception{
//    	MysqlDb mysqldb = new MysqlDb();
//    	mysqldb.connect();
//    	List<Map<String, Object>> list = mysqldb.query("select id,`name` from t_gf_authors where sync = 0 LIMIT 3000");
//    	for(Map<String,Object> mm:list){
//    		System.out.println("更新：bookId :"+mm.get("id")+"   name:"+(mm.get("name")+"").trim());
//    		String name = mm.get("name")+"";
//    		String id = mm.get("id")+"";
//    		List<Map<String, Object>> list2 = mysqldb.query("select kind_one,count(1) num from t_gf_book where author = '"+name+"' GROUP BY kind_one");
//    		int num=0;
//    		int num1=0;
//    		int num2=0;
//    		int num3=0;
//    		int num4=0;
//    		int num5=0;
//    		int num6=0;
//    		if(list2!=null&&list2.size()>0){
//    			for(Map<String,Object> xx:list2){
//    				int kind = Integer.parseInt(xx.get("kind_one")+"");
//    				int nums = Integer.parseInt(xx.get("num")+"");
//    				num+=nums;
//					switch (kind) {
//					case 1:
//						num1=nums;
//						break;
//					case 2:
//						num2=nums;
//						break;
//					case 3:
//						num3=nums;
//						break;
//					case 4:
//						num4=nums;
//						break;
//					case 5:
//						num5=nums;
//						break;
//					case 6:
//						num6=nums;
//						break;
//					default:
//						break;
//					}
//    			}
//    			
//    			mysqldb.execute("update t_gf_authors set sync = 1,works_shi_count="+num1+",works_ci_count="+num2+",works_qu_count="+num3+",works_fu_count="+num4+",works_shu_count="+num5+",works_wen_count="+num6+",works_count="+num+" where id = "+id);
//    		}
//    	}
//	}

//	public static void main(String[] args) {
////		String zfc = "黄机,宋====黄泳,宋====黄澄,明====黄玠,元====黄琮,宋====黄裳,明====黄钺,明====黄锡朋,近现代====黎伯元,元====龙辅,元====龚璛,宋====龚鼎孳,明====黄庚,宋====鲜于枢,元====鸠摩罗什,晋====鹿虔扆,五代====麦孟华,清====黄中,宋====黄华,清====黄学海,清====魏徵,唐====魏承班,五代====魏收,南北朝====魏禧,清====鲁仕能,宋====鲁渊,元====马钰,金====高启,明====高宪,金====高明,元====顾若璞,清====顾观,明====颜师古,唐====马援,汉====韩玉,宋====顾夐,五代====顾德辉,宋====顾济,明====韩奕,元====韦居安,宋====陈洪绶,明====陈淳,宋====陈炜,宋====陈琳,汉====陈绎,宋====陈羽,唐====陈翥,宋====陈肃,宋====陈谏,唐====陈辉,清====陈述,唐====陈道,明====陶宗仪,明====陶弘景,南北朝====陶望龄,明====陈斌,明====陈普,元====陈柏,明====陈植,宋====陈毅,近现代====陈寿,晋====陈师道,宋====陈庚,宋====陈恭尹,明====陈抟,宋====陈政,唐====陆羽,唐====陈书,清====陈于王,清====陈亮,宋====陈匪石,近现代====陈均,宋====陈子昂,唐====陈孚,元====阮逸,宋====阮阅,宋====陆文圭,元====陆树声,明====阎选,五代====阮元,清====郑子聃,金====郑洪,元====郑玉,唐====郦炎,汉====郭居敬,元====郭文,晋====金人瑞,清====金缨,清====钱岳,元====钱泳,清====钱澄之,清====钱绅,明====钱继章,明====钱谦益,清====赵秉文,金====赵蕃,宋====路温舒 ,汉====邓林,明====邝露,明====邬仁卿,清====邯郸淳,三国====邵拙,五代====邹浩,宋====邹阳 ,汉====贾谊,汉====赵可,金====赵壹,汉====赵执信,清====赵文,元====赵昂,宋====谭处端,元====谭献,清====谭用之,五代====贝琼,明====贯休,五代====谈迁,清====谢朓,南北朝====袁燮,宋====解缙,明====詹玉,元====计东,清====许古,金====许宏,唐====许德溥,明====许敬宗,唐====薛道衡,隋====虞世南,唐====虞姬,秦====袁华,明====袁宏道,明====苏滨,宋====苏蕙,晋====苏轼,宋====范咸,清====范成大,宋====范晔,南北朝====莫崙,宋====萧岑,隋====萧德言,唐====萧纶,南北朝====萧统,南北朝====萧衍,南北朝====葛洪,晋====葛胜仲,宋====葛道人,元====董以宁,清====董其昌,明====蒋平阶,明====蒲松龄,清====蓝仁,明====蔡京,宋====蔡琰,汉====蔡襄,宋====蔡邕,汉====薛昭蕴,唐====聂镛,元====胡仔,宋====胡宿,宋====胡珵,宋====胡瑗,宋====胡翰,明====脱脱,元====舒頔,元====苏大年,元====苏小小,南北朝====罗隐,唐====翁宏,五代====秦嘉,汉====程晓,三国====程节,宋====章甫,宋====繁钦,汉====罗志仁,宋====白珽,元====皇甫谧,晋====石岩,清====祝允明,明====祢衡,汉====王赞,宋====王逸,汉====王道,宋====王野,明====王闿运,清====班固,汉====班婕妤,汉====班彪,汉====班昭,汉====申涵光,清====王翰,唐====王艮,元====王莘,宋====王褒,汉====王训,南北朝====王纬,唐====王绩,唐====王寂,金====王履,宋====王应辰,清====王应麟,宋====王度,宋====王庭筠,金====王延寿,汉====王廷圭,唐====王恪,清====王揆,唐====王无咎,宋====王易简,宋====王昶,明====王景,五代====王松,宋====王正己,宋====王汾,宋====王沂,宋====王涣,唐====王清,明====王照,清====王特起,金====王玠,元====王珪,宋====王琪,宋====王畛,元====王磐,明====王礼,元====王竞,金====王筠,清====王夫之,明====王奕,元====王守仁,明====汪东,近现代====汪元量,元====汪琬,清====沈彬,唐====沈性,元====沈曾植,清====沈祥龙,清====沈谦,明====沈贞,元====沙正卿,元====洪迈,宋====淮南小山,汉====清浚,明====滕白,宋====潘岳,晋====潘纬,唐====焦竑,明====熊鉌,宋====爱理沙,元====牛峤,唐====牛希济,五代====牟巘,宋====王仲元,元====王佐,明====王余佑,清====王俊,宋====王公亮,唐====王冕,元====武汉臣,元====段克己,金====毛文锡,五代====毛熙震,五代====江开,清====江总,南北朝====江晖,明====欧阳炯,五代====欧阳詹,唐====欧阳询,唐====梁士楚,明====梁寅,明====梁清标,清====梁鸿,汉====欧大任,明====林震,宋====枚乘,汉====柯培元,清====柳亚子,近现代====柳得恭,清";
////		String[] strsz=zfc.split("====");
////		for(String str:strsz){
////			String[] sz=str.split(",");
////			System.out.println("update t_gf_authors set dynasty = '"+sz[1]+"' where `name` = '"+sz[0]+"' and `name` not in('刘寅','刘肃','张渐','张潮','张登','徐元瑞','徐延寿','徐灿','李冶','李玉','王褒','王谟','薛琼','陈敬','陈沆');");
////		}
//		List<String> name = new ArrayList<>();
//		getAllFileName("D:\\wechat\\WeChat Files\\q2207764q\\Files\\宋\\宋",name);
//		for(String str:name){
//			System.out.println("update t_gf_authors set img = 'http://www.diando.net/img/author/"+str+"' where `name` = LEFT('"+str+"',LOCATE('.','"+str+"')-1);");
////			System.out.println(str.substring(0,str.indexOf(".")));
//		}
//	}
//
//	public static void getAllFileName(String path,List<String> fileNameList) {
//        //ArrayList<String> files = new ArrayList<String>();
//        boolean flag = false;
//        File file = new File(path);
//        File[] tempList = file.listFiles();
//
//        for (int i = 0; i < tempList.length; i++) {
//            if (tempList[i].isFile()) {
////              System.out.println("文     件：" + tempList[i]);
//                //fileNameList.add(tempList[i].toString());
//                fileNameList.add(tempList[i].getName());
//            }
//            if (tempList[i].isDirectory()) {
////              System.out.println("文件夹：" + tempList[i]);
//                getAllFileName(tempList[i].getAbsolutePath(),fileNameList);
//            }
//        }
//        return;
//    }


    //构造函数
    public MysqlDb() throws Exception {
    }

    //获取连接
    public void connect() throws Exception {
        conn = connPool.getConnection();
        if (conn == null)
            throw new Exception("连接不存在");
        return;
    }

    //回收连接
    public void recycleConnect() throws Exception {
        connPool.close(conn);
    }

    //当参数不都是字符串时可以通过此方法查询
    public List<Map<String, Object>> query(String sql, List<Object> params) throws Exception {
        log.info(sql + "\n" + params);
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        try {
            prst = conn.prepareStatement(sql);
            int i = 1;
            if (params.size() != 0) {
                for (Object param : params) {
                    prst.setObject(i++, param);
                }
            }
            rst = prst.executeQuery();
            rsmd = rst.getMetaData();

            //共几列
            int columnCount = rsmd.getColumnCount();
            while (rst.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i + 1).toString(), getValueByFieldClass(rst, rsmd, i + 1));
                }
                dataList.add(map);
            }

        } catch (SQLException e) {
            throw e;
        }
        return dataList;
    }

    //当参数不都是字符串时可以通过此方法查询
    public List<String> queryListStr(String sql) throws Exception {
        log.info(sql);
        List<String> dataList = new ArrayList<String>();

        try {
            prst = conn.prepareStatement(sql);
            rst = prst.executeQuery();
            rsmd = rst.getMetaData();

            while (rst.next()) {
                dataList.add(getValueByFieldClass(rst, rsmd, 1) + "");
            }

        } catch (SQLException e) {
            throw e;
        }
        return dataList;
    }

    //查询一条数据
    public Map<String, Object> first(String sql, List<Object> param) throws Exception {
        List<Map<String, Object>> data = query(sql, param);

        if (data == null || data.isEmpty()) {
            log.info("查询sql为：" + sql + ",参数为：" + param.toString() + "的查询条件的数据集为空");
            return null;
        }

        if (data.size() > 1) {
            log.info("查询sql为：" + sql + ",参数为：" + param.toString() + "的查询条件的数据集大于一条");
            throw new Exception("数据集大于一条");
        }

        //返回结果
        return data.get(0);
    }


    //当参数不都是字符串时可以通过此方法查询
    public ResultSet queryToResultSet(String sql) throws Exception {
        try {
            prst = conn.prepareStatement(sql);
            rst = prst.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rst;
    }

    //执行原生sql查询返回list
    public List<Map<String, Object>> query(String sql) throws Exception {
        log.info(sql);
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        try {
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);
            rsmd = rst.getMetaData();

            //共几列
            int columnCount = rsmd.getColumnCount();
            while (rst.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i + 1).toString(), getValueByFieldClass(rst, rsmd, i + 1));
                }
                dataList.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("SQLException: " + e.getMessage());
        }

        return dataList;
    }

    /**
     * 专门为对账文件查询数据库实现的一个方法。因为用上面的query方法会在getValueByFieldClass方法中报空指针的错
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> query4DealWithFile(String sql) throws Exception {
        log.info(sql);

        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        try {
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);
            rsmd = rst.getMetaData();

            //共几列
            int columnCount = rsmd.getColumnCount();

            while (rst.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i + 1).toString(), rst.getObject(i + 1));
                }
                dataList.add(map);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("SQLException: " + e.getMessage());
        }

        return dataList;
    }


    //查询结果以map方式存储---当参数类型不清楚时,清楚时也可以用
    public Map<String, Map<String, Object>> query(String sql, List<Object> params, String key) throws Exception {
        log.info(sql + "\n参数为" + params);

        Map<String, Map<String, Object>> dataList = new HashMap<String, Map<String, Object>>();

        prst = conn.prepareStatement(sql);
        int i = 1;
        if (params != null && params.size() != 0) {
            for (Object param : params) {
                prst.setObject(i++, param);
            }
        }
        rst = prst.executeQuery();
        rsmd = rst.getMetaData();

        //共几列
        int columnCount = rsmd.getColumnCount();
        while (rst.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            String field_value = "";
            for (i = 0; i < columnCount; i++) {
                String field = rsmd.getColumnLabel(i + 1).toString();
                map.put(field, getValueByFieldClass(rst, rsmd, i + 1));
                if (!key.equals(field)) continue;
                Object value = getValueByFieldClass(rst, rsmd, i + 1);
                if (value != null) field_value = value.toString();
            }
            //map的key
            if ("".equals(field_value)) {
                throw new Exception("该字段[" + key + "]无法作为key");
            }
            dataList.put(field_value, map);
        }

        return dataList;
    }

    //查询结果以map方式存储---当参数类型不清楚时,清楚时也可以用
    public Map<String, Map<String, Object>> query(String sql, List<Object> params, String key1, String key2) throws Exception {
        log.info(sql + "\n参数为" + params);

        Map<String, Map<String, Object>> dataList = new HashMap<String, Map<String, Object>>();

        prst = conn.prepareStatement(sql);
        int i = 1;
        if (params != null && params.size() != 0) {
            for (Object param : params) {
                prst.setObject(i++, param);
            }
        }
        rst = prst.executeQuery();
        rsmd = rst.getMetaData();

        //共几列
        int columnCount = rsmd.getColumnCount();
        while (rst.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            String field_value = "";
            for (i = 0; i < columnCount; i++) {
                String field = rsmd.getColumnLabel(i + 1).toString();
                map.put(field, getValueByFieldClass(rst, rsmd, i + 1));
                if (key1.equals(field) || key2.equals(field)) {
                    Object value = getValueByFieldClass(rst, rsmd, i + 1);
                    if (value != null) field_value = field_value + value.toString() + ",";
                }
            }
            //map的key
            if ("".equals(field_value)) {
                throw new Exception("该字段[" + key1 + "]无法作为key");
            }
            dataList.put(field_value, map);
        }

        return dataList;
    }

    //执行原生sql以map方式存储
    public Map<Object, Map<String, Object>> query(String sql, String key) throws Exception {
        log.info(sql);
        Map<Object, Map<String, Object>> dataList = new HashMap<Object, Map<String, Object>>();

        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        rsmd = rst.getMetaData();

        //共几列
        int columnCount = rsmd.getColumnCount();
        while (rst.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            Object field_value = null;
            for (int i = 0; i < columnCount; i++) {
                String field = rsmd.getColumnLabel(i + 1).toString();
                map.put(field, getValueByFieldClass(rst, rsmd, i + 1));
                if (!key.equals(field)) continue;
                field_value = getValueByFieldClass(rst, rsmd, i + 1);
            }
            //map的key
            if (null == field_value) {
                throw new Exception("该字段[" + key + "]无法作为key");
            }
            dataList.put(field_value, map);
        }

        return dataList;
    }

    public Map<String, Map<String, Object>> squery(String sql, String key) throws Exception {
        log.info(sql);
        Map<String, Map<String, Object>> dataList = new HashMap<String, Map<String, Object>>();

        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        rsmd = rst.getMetaData();

        //共几列
        int columnCount = rsmd.getColumnCount();
        while (rst.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            String field_value = "";
            for (int i = 0; i < columnCount; i++) {
                String field = rsmd.getColumnLabel(i + 1).toString();
                map.put(field, getValueByFieldClass(rst, rsmd, i + 1));
                if (!key.equals(field)) continue;
                field_value = getValueByFieldClass(rst, rsmd, i + 1).toString();
            }
            //map的key
            if (null == field_value) {
                throw new Exception("该字段[" + key + "]无法作为key");
            }
            dataList.put(field_value, map);
        }

        return dataList;
    }

    //根据字段类型调用不同的方法去字段值
    private static Object getValueByFieldClass(ResultSet resultSet, ResultSetMetaData rsMetaData, int i) throws Exception {
        String class_name = null;
        Object ret_obj = null;
        class_name = rsMetaData.getColumnClassName(i).toString();
        if (class_name.equals("java.lang.Integer") || class_name.equals("java.lang.Boolean")
                || class_name.equals("java.math.BigInteger")) {
            ret_obj = resultSet.getInt(rsMetaData.getColumnLabel(i));
        } else if (class_name.equals("java.lang.String")) {
            ret_obj = resultSet.getString(rsMetaData.getColumnLabel(i));
        } else if (class_name.equals("java.math.BigDecimal")) {
            ret_obj = resultSet.getBigDecimal(rsMetaData.getColumnLabel(i));
            if (ret_obj != null && ret_obj.toString().equals("0E-8"))
                return "0.00000000";
        } else if (class_name.equals("java.lang.Long")) {
            ret_obj = resultSet.getLong((rsMetaData.getColumnLabel(i)));
        } else if (class_name.equals("java.sql.Timestamp")) {
            java.util.Date date = resultSet.getTimestamp(rsMetaData.getColumnLabel(i));
            ret_obj = date;
        }
        return ret_obj;
    }

    //执行插入和更新
    public int execute(String sql, List<String> params) throws Exception {
        log.info(sql + "\n参数为" + params);
        int effect_num = 0;
        prst = conn.prepareStatement(sql);
        int i = 1;
        if (params != null && params.size() != 0) {
            for (String param : params) {
                prst.setString(i++, param);
            }
        }
        effect_num = prst.executeUpdate();
        return effect_num;
    }

    //执行插入和更新，不清楚字段类型时
    public int execute(String sql, List<Object> params, Boolean fuzzy) throws Exception {
        log.info(sql + "\n参数为" + params);
        int effect_num = 0;
        prst = conn.prepareStatement(sql);
        int i = 1;
        if (params != null && params.size() != 0) {
            for (Object param : params) {
                prst.setObject(i++, param);
            }
        }

        effect_num = prst.executeUpdate();
        return effect_num;
    }

    //执行插入
    public int insert(String table, Map<String, Object> fieldsValue) throws Exception {
        List<Object> params = new ArrayList<Object>();
        List<String> tables_headers = prepareInsertSql(fieldsValue, params);

        //组装sql
        String insert_sql = "INSERT INTO " + table + " (" + tables_headers.get(0) + ") VALUES(" + tables_headers.get(1) + ") ;";

        //执行结果
        int effect_num = 0;
        effect_num = execute(insert_sql, params, true);
        return effect_num;
    }

    //执行插入
    public void berthInsert(String table, List<Map<String, Object>> fieldsValue) throws Exception {

        List<String> sqlList = new ArrayList<String>();
        for (int i = 0; i < fieldsValue.size(); i++) {
            String sql = assemblyInsertSql(table, fieldsValue.get(i));

            sqlList.add(sql);
        }

        executeBatch(sqlList);
    }

    //处理数据
    protected static List<String> prepareInsertSql(Map<String, Object> fields, List<Object> params) throws Exception {
        List<String> table_fields = new ArrayList<String>();
        String values = "";

        for (String field : fields.keySet()) {
            table_fields.add("`" + field + "`");
            params.add(fields.get(field));
            values += ",?";
        }

        String table_headers = table_fields.toString();
        table_headers = table_headers.substring(1, table_headers.length() - 1);

        //组合结果返回
        List<String> p_insert = new ArrayList<String>();
        p_insert.add(table_headers);
        p_insert.add(values.substring(1));
        return p_insert;
    }

    /**
     * 可以执行添加操作 返回id
     *
     * @param tableName
     * @param Map<String,String>
     * @return
     */
    public String executeInsertById(String tableName, Map<String, Object> params) throws Exception {
        return execDB(assemblyInsertSql(tableName, params));
    }

    /**
     * 可以执行添加操作 返回id
     *
     * @param tableName
     * @param Map<String,String>
     * @return
     */
    public String executeInsertByIds(String tableName, Map<String, Object> params) throws Exception {
        return execDB(assemblyInsertSql(tableName, params));
    }

    public String assemblyInsertSql(String tableName, Object obj) {
        Map params = (Map) obj;
        String sql = "insert into " + tableName;
        String paramHead = "(";
        String paramEnd = " value(";
        Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext(); ) {
            String s = (String) it.next();
            if (s != null && !s.equals("")) {
                Object field_value = params.get(s);
                if (field_value == null) continue;
                paramHead += s + ",";
                paramEnd += "'" + field_value + "',";
            }
        }
        paramHead = paramHead.substring(0, paramHead.length() - 1);
        paramEnd = paramEnd.substring(0, paramEnd.length() - 1);
        sql += paramHead + ")" + paramEnd + ")";
        return sql;
    }

    public String[] assemblyInsertSqls(String tableName, Object obj) {
        String[] strSql = new String[3];
        Map params = (Map) obj;
        String sql = "insert into " + tableName;
        String paramHead = "(";
        String paramEnd = "(";
        Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext(); ) {
            String s = (String) it.next();
            if (s != null && !s.equals("")) {
                String str = params.get(s) + "";
                paramHead += s + ",";
                paramEnd += "'" + str + "',";
            }
        }
        paramHead = paramHead.substring(0, paramHead.length() - 1);
        paramEnd = paramEnd.substring(0, paramEnd.length() - 1);
        sql += paramHead + ") value ";
        strSql[0] = sql;
        strSql[1] = paramEnd + ")";
        strSql[2] = sql + paramEnd + ")";
        return strSql;
    }

    /**
     * 判断字符串中是否包含汉字
     *
     * @param str
     * @return
     */
    public static boolean isChinese(String str) {
        Pattern pattern = Pattern.compile("[\u4E00-\u9FA5]");
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            return true;
        }
        return false;
    }

    public String assemblyUpdateSql(String tableName, Map<String, Object> params, Map<String, String[]> conditionMap, String sqlAdditional, Boolean bool) {
        String sql = "update " + tableName;
        String paramHead = " set ";
        String paramEnd = "";
        if (params != null && params.size() <= 0) return "";

        Set<String> key = params.keySet();
        for (Iterator it = key.iterator(); it.hasNext(); ) {
            String s = (String) it.next();
            if (s != null && !s.equals("")) {
                String str = params.get(s) + "";
                //没有加减算法就
                if ((str.indexOf("+") > 0 || str.indexOf("-") > 0) && bool) {
                    if (isChinese(str))
                        paramHead += s + "='" + str + "',";
                    else
                        paramHead += s + "=" + str + ",";
                } else
                    paramHead += s + "='" + str + "',";
            }
        }
        paramHead = paramHead.substring(0, paramHead.length() - 1);

        if (conditionMap != null && conditionMap.size() > 0) {
            paramEnd += " where";
            Set<String> keys = conditionMap.keySet();
            for (Iterator it = keys.iterator(); it.hasNext(); ) {
                String s = (String) it.next();
                if (s != null && !s.equals("")) {
                    String[] str = conditionMap.get(s);
                    if (str.length == 2) {
                        paramEnd += " " + s + " " + str[0] + " " + str[1] + " and";
                    }
                }
            }
            paramEnd = paramEnd.substring(0, paramEnd.length() - 4);
        }

        if (sqlAdditional != null && !sqlAdditional.equals("")) {
            if (conditionMap.size() > 0)
                paramEnd += sqlAdditional;
            else
                paramEnd += " where " + sqlAdditional;
        }
        sql += paramHead + paramEnd;
        System.out.println(sql);
        return sql;
    }

    //更新数据
    public int update(String tablename, Map<String, Object> replace_value, Map<String, Object> add_value, Map<String, Object> condition) throws Exception {
        //组合sql
        String update_sql = "";
        List<Object> field_value_param = new ArrayList<Object>();
        update_sql = buildUpdateSql(tablename, replace_value, add_value, condition, field_value_param);

        //声明更新条数
        int effect_num = 0;
        effect_num = execute(update_sql, field_value_param, true);

        return effect_num;
    }

    //构造更新sql，累加替换和更新替换
    public static String buildUpdateSql(String tablename, Map<String, Object> replace_value, Map<String, Object> add_value, Map<String, Object> condition, List<Object> all_field_value) throws Exception {
        //声明sql
        String update_sql = "Update " + tablename + " SET ";

        //字段值的更新替换设置
        if (replace_value != null && !replace_value.isEmpty())
            for (String field : replace_value.keySet()) {
                update_sql += "`" + field + "` = ? ,";
                all_field_value.add(replace_value.get(field));
            }

        //累加字段的值
        if (add_value != null && !add_value.isEmpty())
            for (String field : add_value.keySet()) {
                update_sql += "`" + field + "` = `" + field + "` + ? ,";
                all_field_value.add(add_value.get(field));
            }
        update_sql = update_sql.substring(0, update_sql.length() - 1);
        update_sql += " WHERE ";

        //更新条件
        for (String field : condition.keySet()) {
            update_sql += " `" + field + "` = ? AND";
            all_field_value.add(condition.get(field));
        }
        update_sql = update_sql.substring(0, update_sql.length() - 3);
        return update_sql;
    }

    /**
     * 可以执行修改操作
     *
     * @param tableName
     * @param head      params
     * @param 条件        conditionMap
     * @param 追加条件      sqlAdditional
     * @return
     */
    public int executeUpdate(String tableName, Map<String, Object> params, Map<String, String[]> conditionMap, String sqlAdditional) throws Exception {
        return execute(assemblyUpdateSql(tableName, params, conditionMap, sqlAdditional, true));
    }

    //执行原生sql
    public int execute(String sql) throws Exception {
        log.info(sql);
        int effect_num = 0;

        stmt = conn.createStatement();
        effect_num = stmt.executeUpdate(sql);

        return effect_num;
    }

    public String execDB(String sql) throws Exception {
        log.info(sql);

        String effect_num = "";
        ResultSet rs = null;
        prst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        effect_num = prst.executeUpdate() + "";
        rs = prst.getGeneratedKeys();
        if (rs.next()) {
            effect_num = rs.getString(1);
        }
        return effect_num;
    }

    /**
     * @param sql
     * @param Map中是String[] key 是 列名 string[0] 是比较操作符 如： like
     *                      string[1] 是列 对应的值
     * @param sql补充参数       添加在sql最外层
     * @return List<Map < String, Object>>
     * @throws Exception
     */
    public List<Map<String, Object>> query(String sql, Map<String, String[]> params, String sqlAdditional) throws Exception {

        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        try {
            if (params != null && params.size() > 0) {
                sql += " where";
                Set<String> key = params.keySet();
                for (Iterator it = key.iterator(); it.hasNext(); ) {
                    String s = (String) it.next();
                    if (s != null && !s.equals("")) {
                        String[] str = params.get(s);
                        if (str.length == 2) {
                            sql += " " + s + " " + str[0] + " " + str[1];
                        }
                    }
                }
            }

            if (sqlAdditional != null && !sqlAdditional.equals("")) {
                if (params.size() > 0)
                    sql += sqlAdditional;
                else
                    sql += " where " + sqlAdditional;
            }
            log.info(sql);

            prst = conn.prepareStatement(sql);
            rst = prst.executeQuery();
            rsmd = rst.getMetaData();
            //共几列
            int columnCount = rsmd.getColumnCount();
            while (rst.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i + 1).toString(), getValueByFieldClass(rst, rsmd, i + 1));
                }
                dataList.add(map);
            }

        } catch (SQLException e) {
            throw e;
        }
        return dataList;
    }

    public List<Map<String, Object>> getQueryTable(String paramString, Map map) throws Exception {
        if (map != null && map.size() > 0) {
            paramString += " where";
            Set<String> keys = map.keySet();
            for (Iterator it = keys.iterator(); it.hasNext(); ) {
                String s = (String) it.next();
                if (s != null && !s.equals("")) {
                    String str = map.get(s).toString();
                    if (!s.equals("trans_passwd"))
                        paramString += " " + s + " = '" + str + "' and";
                }
            }
            paramString = paramString.substring(0, paramString.length() - 4);
        }
        return query(paramString);
    }

    public static int[] executeBatch(List<String> sqls) throws Exception {
        if (sqls == null)
            return null;
        stmt = conn.createStatement();
        for (String sql : sqls) {
            //log.info(sql);
            stmt.addBatch(sql);
        }
        return stmt.executeBatch();
    }

    public static void executeBatchs(List<String[]> sqls) throws Exception {
        if (sqls == null)
            return;

        List<String> executesql = new ArrayList<String>();
        int BatchNum = 100;
        String strSqls = "";
        int i = 1;
        for (String[] sql : sqls) {
            if (i == 1)
                strSqls += sql[2] + ",";
            else
                strSqls += sql[1] + ",";

            if (i == BatchNum) {
                strSqls = strSqls.substring(0, strSqls.length() - 1);
                executesql.add(strSqls);
                strSqls = "";
                i = 0;
            }
            i++;
        }
        strSqls = strSqls.substring(0, strSqls.length() - 1);
        executesql.add(strSqls);
        i--;
        stmt = conn.createStatement();

        for (String sql : executesql) {
            log.info(sql);
            stmt.execute(sql);
        }
    }

    //开启事务
    public void startTrans() throws Exception {
        conn.setAutoCommit(false);
    }

    //提交事务
    public void commit() throws Exception {
        conn.commit();
        conn.setAutoCommit(true);
    }

    //回滚事务
    public void rollback() throws Exception {
        conn.rollback();
        conn.setAutoCommit(true);
    }

    public void close() throws Exception {
        try {
            if (null != conn) conn.close();
            if (null != prst) prst.close();
            if (null != rst) rst.close();
            if (null != stmt) stmt.close();
        } catch (SQLException e) {
            throw new Exception("关闭连接时失败：" + e.getMessage());
        }
    }

    public void free() throws Exception {
        try {
            prst.clearParameters();
        } catch (SQLException e) {
            throw new Exception("清除当前的查询值时出错：" + e.getMessage());
        }
    }
}
